var express = require('express');
var router = express.Router();
var utils = require('../../functions/utils');

router.get('/' , function(req,res)
{
	var html = "<form name='tmpForm' method='post' action='/userStatistics'></form>";
	html +=	"<script language=javascript>tmpForm.submit();</script>";

	res.writeHead(200 , {'Content-Type' : 'text/html; charset=utf-8'});
	res.end(html);

});

function makeSelectDateTypeString(type)
{
		var str  = '';

		str += "<option  value='day' ";
		if(type == 'day')
			str += "SELECTED ";
		str += ">DAY</option>";

		str += "<option  value='hour' ";
		if(type == 'hour')
			str += "SELECTED ";
		str += ">HOUR</option>";

		return str;
}

function makeSelectOsTypeString(type)
{
		var str  = '';

		str += "<option  value='all' ";
		if(type == 'all')
			str += "SELECTED ";
		str += ">ALL</option>";

		str += "<option  value='android' ";
		if(type == 'android')
			str += "SELECTED ";
		str += ">Android</option>";

		str += "<option  value='ios' ";
		if(type == 'ios')
			str += "SELECTED ";
		str += ">iOS</option>";

		return str;
}

function makeSelectLoginTypeString(type)
{
		var str  = '';

		str += "<option  value='all' ";
		if(type == 'all')
			str += "SELECTED ";
		str += ">ALL</option>";

		str += "<option  value='email' ";
		if(type == 'email')
			str += "SELECTED ";
		str += ">Email</option>";

		str += "<option  value='facebook' ";
		if(type == 'facebook')
			str += "SELECTED ";
		str += ">Facebook</option>";

		return str;
}

function makeSelectOrderTypeString(type)
{
		var str  = '';

		str += "<option  value='dateDescending' ";
		if(type == 'dateDescending')
			str += "SELECTED ";
		str += ">▼ DATE</option>";

		str += "<option  value='dateAscending' ";
		if(type == 'dateAscending')
			str += "SELECTED ";
		str += ">▲ DATE</option>";

		str += "<option  value='countDescending' ";
		if(type == 'countDescending')
			str += "SELECTED ";
		str += ">▼ JOIN COUNT</option>";

		str += "<option  value='countAscending' ";
		if(type == 'countAscending')
			str += "SELECTED ";
		str += ">▲ JOIN COUNT</option>";

		str += "<option  value='conCountDescending' ";
		if(type == 'conCountDescending')
			str += "SELECTED ";
		str += ">▼ ACTIVE COUNT</option>";

		str += "<option  value='conCountAscending' ";
		if(type == 'conCountAscending')
			str += "SELECTED ";
		str += ">▲ ACTIVE COUNT</option>";

		str += "<option  value='nationDescending' ";
		if(type == 'nationDescending')
			str += "SELECTED ";
		str += ">▼ NATION</option>";

		str += "<option  value='nationAscending' ";
		if(type == 'nationAscending')
			str += "SELECTED ";
		str += ">▲ NATION</option>";

		str += "<option  value='userArticleDescending' ";
		if(type == 'userArticleDescending')
			str += "SELECTED ";
		str += ">▼ USER ARTICLE</option>";

		str += "<option  value='userArticleAscending' ";
		if(type == 'userArticleAscending')
			str += "SELECTED ";
		str += ">▲ USER ARTICLE</option>";


		return str;
}


function makeSelectYearString(year)
{
		var str  = '';
		for(var i = 2014 ; i <= (new Date()).getFullYear() ; i ++)
		{
			if(year == i)
				str += "<option value='" + i + "' SELECTED>"+i+"</option>";
			else
				str += "<option value='" + i + "'>"+i+"</option>";
		}

		return str;
};

function makeSelectMonthString(mon)
{
		var str  = '';
		for(var i = 1 ; i <= 12 ; i ++)
		{
			if(mon == i)
				str += "<option value='" + utils.attachZero(i,2) + "' SELECTED>" + utils.attachZero(i,2) + "</option>";
			else
				str += "<option value='" + utils.attachZero(i,2) + "'>" + utils.attachZero(i,2) + "</option>";
		}

		return str;
};

function makeSelectDayString(day)
{
		var str  = '';
		for(var i = 1 ; i <= 31 ; i ++)
		{
			if(day == i)
				str += "<option value='" + utils.attachZero(i,2) + "' SELECTED>" + utils.attachZero(i,2) + "</option>";
			else
				str += "<option value='" + utils.attachZero(i,2) + "'>" + utils.attachZero(i,2) + "</option>";
		}

		return str;
};

function makeSelectHourString(hour)
{
		var str  = '';
		for(var i = 0 ; i <= 23 ; i ++)
		{
			if(hour == i)
				str += "<option value='" + utils.attachZero(i,2) + "' SELECTED>" + utils.attachZero(i,2) + "</option>";
			else
				str += "<option value='" + utils.attachZero(i,2) + "'>" + utils.attachZero(i,2) + "</option>";
		}

		return str;
};

function makeSelectMinSecString(ms)
{
		var str  = '';
		for(var i = 0 ; i <= 59 ; i ++)
		{
			if(ms == i)
				str += "<option value='" + utils.attachZero(i,2) + "' SELECTED>" + utils.attachZero(i,2) + "</option>";
			else
				str += "<option value='" + utils.attachZero(i,2) + "'>" + utils.attachZero(i,2) + "</option>";
		}

		return str;
};

function saveFile(filename , str)
{
	//save csv file
	require('fs').writeFile(global.config.storage.statistics.localDirectory + filename, str, function(err) {
			if(err) throw err;
			console.log('csv File write completed');
		});
}

function makeUserJoinDataSheet(data , csvFilename)
{
	var str = '<thead><tr bgcolor=#9BDCF2 ><th>&nbsp;<b>NO&nbsp;</th><th>&nbsp;<b>DATE&nbsp;</th><th>&nbsp;<b>JOIN COUNT&nbsp;</th><th>&nbsp;<b>ACTIVE COUNT&nbsp;</th></tr></thead><tbody>';
	var csv = "\"NO\",\"DATE\",\"JOIN COUNT\",\"ACTIVE COUNT\"";

	var total = 0;
	var total2 = 0;
	for(var i = 0 ; i < data.length ; i  ++)
	{
		str += '<tr><td>&nbsp;'+(i+1)+'&nbsp;</td><td>&nbsp;'+data[i].date+'&nbsp;</td><td>&nbsp;'+data[i].count+'&nbsp;</td><td>&nbsp;'+data[i].conCount+'&nbsp;</td></tr>';		
		csv += "\r\n\""+(i+1)+"\","+data[i].date+","+data[i].count+","+data[i].conCount;

		total += data[i].count;
		total2 += data[i].conCount;
	}

	str += '<tfoot><tr bgcolor=#CCD9DE><Td colspan=2><b>TOTAL</td><td>'+total+'</td><td>'+total2+'</td></tr></tfoot>';
	str += '</tbody>';

	saveFile(csvFilename , csv);

	return str;
}

function makeNationDataSheet(data , csvFilename)
{
	var str = '<thead><tr bgcolor=#9BDCF2 ><th>&nbsp;<b>NO&nbsp;</th><th>&nbsp;<b>COUNTRY CODE&nbsp;</th><th>&nbsp;<b>JOIN COUNT&nbsp;</th></tr></thead><tbody>';
	var csv = "\"NO\",\"COUNTRY CODE\",\"JOIN COUNT\"";

	var total = 0;
	for(var i = 0 ; i < data.length ; i  ++)
	{
		str += '<tr><td>&nbsp;'+(i+1)+'&nbsp;</td><td>&nbsp;'+data[i].countryCode+'&nbsp;</td><td>&nbsp;'+data[i].count+'&nbsp;</td></tr>';		
		csv += "\r\n\""+(i+1)+"\","+data[i].countryCode+","+data[i].count;

		total += data[i].count;
	}

	str += '<tfoot><tr bgcolor=#CCD9DE><Td colspan=2><b>TOTAL</td><td>'+total+'</td></tr></tfoot>';
	str += '</tbody>';

	saveFile(csvFilename , csv);

	return str;
}

function makeUserArticleDataSheet(data , csvFilename)
{
	var str = '<thead><tr bgcolor=#9BDCF2 ><th>&nbsp;<b>NO&nbsp;</th><th>&nbsp;<b>NICK&nbsp;</th><th>&nbsp;<b>EMAIL&nbsp;</th><th>&nbsp;<b>LOGIN TYPE&nbsp;</th><th>&nbsp;<b>FOLLOWER&nbsp;</th><th>&nbsp;<b>FOLLOWING&nbsp;</th><th>&nbsp;<b>TOTAL ARTICLE&nbsp;</th><th>&nbsp;<b>EXISTING ARTICLE&nbsp;</th><th>&nbsp;<b>IN RANGE ARTICLE&nbsp;</th></tr></thead><tbody>';
	var csv = "\"NO\",\"NICK\",\"EMAIL\",\"LOGIN TYPE\",\"FOLLOWER\",\"FOLLOWING\",\"TOTAL ARTICLE\",\"EXISTING ARTICLE\",\"IN RANGE ARTICLE\"";

	var total = 0;
	for(var i = 0 ; i < data.length ; i  ++)
	{
		str += '<tr><td>&nbsp;'+(i+1)+'&nbsp;</td><td><img width=40 height=40 src="'+data[i].profilePicUrl+'">&nbsp;'+data[i].userNick+'&nbsp;</td><td>&nbsp;'+data[i].userEmail+'&nbsp;</td><td>&nbsp;'+data[i].loginType+'&nbsp;</td><td>&nbsp;'+data[i].followerCount+'&nbsp;</td><td>&nbsp;'+data[i].followingCount+'&nbsp;</td><td>&nbsp;'+data[i].totalArticle+'&nbsp;</td><td>&nbsp;'+data[i].existingArticle+'&nbsp;</td><td>&nbsp;'+data[i].inRangeArticle+'&nbsp;</td></tr>';		
		csv += "\r\n\""+(i+1)+"\",\""+data[i].userNick+"\",\""+data[i].userEmail+"\",\""+data[i].loginType+"\","+data[i].followerCount+","+data[i].followingCount+","+data[i].totalArticle+","+data[i].existingArticle+","+data[i].inRangeArticle;

		total += data[i].inRangeArticle;
	}

	str += '<tfoot><tr bgcolor=#CCD9DE><Td colspan=8><CENTER><b>TOTAL</td><td>'+total+'</td></tr></tfoot>';
	str += '</tbody>';

	saveFile(csvFilename , csv);

	return str;

}



router.post('/', function(req,res)
{
	var sessionKey = req.session.key;

	if(utils.isValidString(sessionKey))
	{
		var async = require('async');
		async.waterfall([
			function(cb)
			{
				utils.checkManagerSession(sessionKey , cb);
			}
			,function(result , cb) 
			{
				// 실패 - 로그인 페이지 
				if(result == 0)
				{
					utils.notifyManagerSessionExpired(req , res);
				}
				// 성공 - 실제 작업코드.
				else
				{	
				/////////////////////////////////////////////////////////////////////////////////////////////	
				/*
					Input parameters 

					dateType
					osType
					loginType
					orderType

					startYear
					startMonth
					startDay
					startHour
					startMin
					startSec
												   
					endYear
					endMonth
					endDay
					endHour
					endMin
					endSec

				*/

					var dateType = req.body.dateType;		
					var osType = req.body.osType;		
					var loginType = req.body.loginType;		
					var orderType = req.body.orderType;		

					if(!utils.isValidString(dateType))
						dateType = 'day';
					if(!utils.isValidString(osType))
						osType = 'all';
					if(!utils.isValidString(loginType))
						loginType = 'all';
					if(!utils.isValidString(orderType))
						orderType = 'dateDescending';


					var startYear = req.body.startYear;		
					var startMonth = req.body.startMonth;		
					var startDay = req.body.startDay;		
					var startHour = req.body.startHour;		
					var startMin = req.body.startMin;		
					var startSec = req.body.startSec;		

					var endYear = req.body.endYear;		
					var endMonth = req.body.endMonth;		
					var endDay = req.body.endDay;		
					var endHour = req.body.endHour;		
					var endMin = req.body.endMin;		
					var endSec = req.body.endSec;		

					var db = global.replica_db;
					var logDb = global.log_db;

					// 안넘어오면 오늘날짜 첨부터 끝까지 
					if(!utils.isValidString(startYear))
					{
						var today = new Date();
						var ago = new Date();
						ago.setDate(today.getDate() - 14); // 15일 동안.

						startYear = String(ago.getFullYear());
						startMonth = utils.attachZero(ago.getMonth()+1 , 2); //January is 0!
						startDay = utils.attachZero(ago.getDate() , 2);
						startHour = startMin = startSec = '00';

						endYear = String(today.getFullYear());
						endMonth = utils.attachZero(today.getMonth()+1 , 2); //January is 0!
						endDay = utils.attachZero(today.getDate() , 2);
						/*
						endHour= utils.attachZero(today.getUTCHours(), 2);
						endMin = utils.attachZero(today.getUTCMinutes(), 2);
						endSec = utils.attachZero(today.getUTCSeconds(), 2);
						*/
						endHour= 23;
						endMin = 59;
						endSec = 59;

					}

					var startDate = startYear+'-'+startMonth+'-'+startDay+' '+startHour+':'+startMin+':'+startSec;
					var endDate = endYear+'-'+endMonth+'-'+endDay+' '+endHour+':'+endMin+':'+endSec;


					var fs = require('fs');
					fs.readFile('./template/userStatistics' , function(error,data)
					{
						if(error)
						{
							throw error;
						}
						var html = String(data);


						db.connection.getConnection(function(err,connection)
						{
							if(err)
							{
								throw err;
							}

							var ot = orderType;

							// log db 결과로 소팅한다.
							if(orderType == 'conCountDescending' || orderType == 'conCountAscending')
							{
								ot = 'countDescending';
							}
							
							var input_param = [dateType, osType, loginType, ot, startDate, endDate];

							var sql = 'call manage_userStatistics(?, ?, ?, ?, ?, ?)';

							var query = connection.query(sql, input_param , function (err, rows) 
							{

								connection.release();
								if(err)
								{
									throw err;
								}

								var strSelect = makeSelectDateTypeString(dateType);
								html = html.split("%DATE_TYPE%").join(strSelect); 

								strSelect = makeSelectOsTypeString(osType);
								html = html.split("%OS_TYPE%").join(strSelect); 

								strSelect = makeSelectLoginTypeString(loginType);
								html = html.split("%LOGIN_TYPE%").join(strSelect); 

								strSelect = makeSelectOrderTypeString(orderType);
								html = html.split("%ORDER_TYPE%").join(strSelect); 

								if(orderType=='nationDescending'||orderType=='nationAscending'||orderType=='userArticleDescending'||orderType=='userArticleAscending')
									html = html.split("%DATE_SELCT_BLOCK_MODE%").join('none'); 
								else
									html = html.split("%DATE_SELCT_BLOCK_MODE%").join('block'); 

								strSelect = makeSelectYearString(Number(startYear));
								html = html.split("%START_YEAR%").join(strSelect); 
								strSelect = makeSelectMonthString(Number(startMonth));
								html = html.split("%START_MONTH%").join(strSelect); 
								strSelect = makeSelectDayString(Number(startDay));
								html = html.split("%START_DAY%").join(strSelect); 
								strSelect = makeSelectHourString(Number(startHour));
								html = html.split("%START_HOUR%").join(strSelect); 
								strSelect = makeSelectMinSecString(Number(startMin));
								html = html.split("%START_MIN%").join(strSelect); 
								strSelect = makeSelectMinSecString(Number(startSec));
								html = html.split("%START_SEC%").join(strSelect); 


								strSelect = makeSelectYearString(Number(endYear));
								html = html.split("%END_YEAR%").join(strSelect); 
								strSelect = makeSelectMonthString(Number(endMonth));
								html = html.split("%END_MONTH%").join(strSelect); 
								strSelect = makeSelectDayString(Number(endDay));
								html = html.split("%END_DAY%").join(strSelect); 
								strSelect = makeSelectHourString(Number(endHour));
								html = html.split("%END_HOUR%").join(strSelect); 
								strSelect = makeSelectMinSecString(Number(endMin));
								html = html.split("%END_MIN%").join(strSelect); 
								strSelect = makeSelectMinSecString(Number(endSec));
								html = html.split("%END_SEC%").join(strSelect); 

							

								var dd = new Date();
								var csvFilename = 'user_'	+ utils.attachZero(dd.getUTCFullYear() , 4) + utils.attachZero(dd.getUTCMonth()+1 , 2) + utils.attachZero(dd.getUTCDate(),2) 
										+ utils.attachZero(dd.getUTCHours(),2) + utils.attachZero(dd.getUTCMinutes(),2)+ utils.attachZero(dd.getUTCSeconds(),2) + '.csv'; 


								// 이 항목으로 소팅할 경우는 접속자 수도 넣는다.
								if(orderType == 'dateDescending' || orderType == 'dateAscending' 
										|| orderType == 'countDescending' || orderType == 'countAscending'
										|| orderType == 'conCountDescending' || orderType == 'conCountAscending')
								{
									/////////////////// 로그디비에서 일별 접속자 개수 얻어온다. /////////////////
									logDb.connection.getConnection(function(err,connection)
									{
										if(err)
										{
											throw err;
										}

										
										var input_param2 = [startDate, endDate, orderType, osType];

										console.log(input_param2);

										var sql2 = 'call manage_getConnectedUserCount(?, ?, ?, ?)';

										var query2 = connection.query(sql2, input_param2 , function (err, rows2) 
										{
											connection.release();
											if(err)
											{
												throw err;
											}

											// log db 결과로 소팅
											if(orderType == 'conCountDescending' || orderType == 'conCountAscending')
											{

												for(var i = 0 ; i < rows[0].length ; i ++)
												{
													for(var j = 0 ; j < rows2[0].length ; j ++)
													{
														if(rows[0][i].date == rows2[0][j].date)
														{
															var tmp = rows2[0][j].count;

															// rows2 순서를 유지해야 하기 때문에 rows2로 복사한다.
															rows2[0][j] = rows[0][i];
															rows2[0][j].conCount=tmp;

															break;
														}

													}
												}
												// 사용하는게 rows 이므로 복사해버린다.
												rows[0] = rows2[0];

											}
											// replica db 결과로 소팅
											else
											{
												for(var i = 0 ; i < rows2[0].length ; i ++)
												{
													for(var j = 0 ; j < rows[0].length ; j ++)
													{
														if(rows2[0][i].date == rows[0][j].date)
														{
															rows[0][j].conCount=rows2[0][i].count;
															break;
														}

													}
												}

											}


											html = html.split("%DATA_SHEET%").join(makeUserJoinDataSheet(rows[0],csvFilename));

											// html안의 경로
											html = html.split("%EXCEL_FILENAME%").join(csvFilename);

											res.writeHead(200 , {'Content-Type' : 'text/html; charset=utf-8'});
											res.end(html);

										});
									});
									////////////////////////////////////////////////////////

								}
								else
								{
	
									// 화면만들고 csv파일만든다.				
									if(orderType=='nationDescending'||orderType=='nationAscending')
										html = html.split("%DATA_SHEET%").join(makeNationDataSheet(rows[0],csvFilename));
									else if (orderType=='userArticleDescending'||orderType=='userArticleAscending')
										html = html.split("%DATA_SHEET%").join(makeUserArticleDataSheet(rows[0],csvFilename));


									// html안의 경로
									html = html.split("%EXCEL_FILENAME%").join(csvFilename);

									res.writeHead(200 , {'Content-Type' : 'text/html; charset=utf-8'});
									res.end(html);
								}


							}); // query

						}); // getConnection

					}); // readfile
			//////////////////////////////////////////////////////////////////////////////////////////

				} // result==1 
			}
		]); //waterfall
	}
	// 암것도 없으면 loginPage
	else
	{
		utils.gotoManagerLoginPage(req , res);
	}
});

module.exports = router;